﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataAccessLayer
{
    public static class ReservationDB
    {
        public static List<Reservation> GetReservationsUser(string userid)
        {
            List<Reservation> myReservations = new List<Reservation>();
            SqlConnection con = EF4_SQLserverDB.GetConnection();
            SqlCommand cmd = new SqlCommand();
            cmd.Parameters.Add("@userid", SqlDbType.VarChar);
            cmd.Parameters["@userid"].Value = userid;
            cmd.CommandText = "SELECT r.Id, r.CompanyId, r.StartDate, r.EndDate, r.LocationId, l.Name as LName, r.CreateDate, r.UserId, c.Name as CName From Reservation r, Company c, Location l WHERE UserId = @userid AND r.CompanyId = c.Id AND R.LocationId = l.Id";
            cmd.Connection = con;

            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Reservation reservation = new Reservation();
                    reservation.Id = (int)reader["Id"];
                    reservation.StartDate = (DateTime)reader["StartDate"];
                    reservation.EndDate = (DateTime)reader["EndDate"];
                    reservation.CompanyId = (int)reader["CompanyId"];
                    reservation.LocationId = (int)reader["LocationId"];
                    reservation.CreateDate = (DateTime)reader["CreateDate"];
                    reservation.UserId = reader["UserId"].ToString();
                    reservation.CompanyName = reader["CName"].ToString();
                    reservation.LocationName = reader["LName"].ToString();
                    myReservations.Add(reservation);
                }
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return myReservations;
        }


        public static int InsertReservation(Reservation r, string userid)
        {

            int aantal;
            DateTime createDate = DateTime.Now;

            // Connectie aanmaken
            SqlConnection connection = EF4_SQLserverDB.GetConnection();

            // Data opvragen uit de DB
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "Insert into Reservation (StartDate, EndDate, CompanyId, LocationId, CreateDate, UserId) VALUES (@SartDate, @EndDate, @CompanyId, @LocationId, @CreateDate, @UserId)";

            try
            {
                // Connectie openen
                connection.Open();

                // Parameter voorzien aan Query
                command.Parameters.AddWithValue("@SartDate", r.StartDate);
                command.Parameters.AddWithValue("@EndDate", r.EndDate);
                command.Parameters.AddWithValue("@CompanyId", r.CompanyId);
                command.Parameters.AddWithValue("@LocationId", r.LocationId);
                command.Parameters.AddWithValue("@CreateDate", createDate);
                command.Parameters.AddWithValue("@UserId", userid);

                //query uitvoeren
                aantal = command.ExecuteNonQuery();

            }

            catch (InvalidOperationException exception)
            {
                throw;
            }
            
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                // Connectie sluiten
                connection.Close();
            }
            return aantal;
        }

        public static int DeleteReservation(Reservation r)
        {

            int aantal;

            // Connectie aanmaken
            SqlConnection connection = EF4_SQLserverDB.GetConnection();

            // Data opvragen uit de DB
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "DELETE FROM Reservation WHERE Id = @Reservation_ID;";

            try
            {
                // Connectie openen
                connection.Open();

                // Parameter voorzien aan Query
                command.Parameters.AddWithValue("@Reservation_ID", r.Id);

                //query uitvoeren
                aantal = command.ExecuteNonQuery();

            }
            catch (NullReferenceException ex)
            {
                return aantal = 0;
            }

            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                // Connectie sluiten
                connection.Close();
            }
            return aantal;
        }

        public static int UpdateReservation(Reservation r, string userid)
        {
            int aantal;
            DateTime createDate = DateTime.Now;

            // Connectie aanmaken
            SqlConnection connection = EF4_SQLserverDB.GetConnection();

            // Data opvragen uit de DB
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "UPDATE Reservation SET StartDate=@StartDate,EndDate=@EndDate,CompanyId = @CompanyId, LocationId=@LocationId, CreateDate = @CreateDate, UserId = @UserId WHERE Id=@ID;";

            try
            {
                // Connectie openen
                connection.Open();

                // Parameter voorzien aan Query
                command.Parameters.AddWithValue("@ID", r.Id);
                command.Parameters.AddWithValue("@StartDate", r.StartDate);
                command.Parameters.AddWithValue("@EndDate", r.EndDate);
                command.Parameters.AddWithValue("@CompanyId", r.CompanyId);
                command.Parameters.AddWithValue("@LocationId", r.LocationId);
                command.Parameters.AddWithValue("@CreateDate", createDate);
                command.Parameters.AddWithValue("@UserId", userid);

                //query uitvoeren
                aantal = command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                // Connectie sluiten
                connection.Close();
            }
            return aantal;
        }


        public static List<Reservation> GetReservationsDate(string date)
        {
            List<Reservation> myReservations = new List<Reservation>();
            SqlConnection con = EF4_SQLserverDB.GetConnection();
            SqlCommand cmd = new SqlCommand();
            cmd.Parameters.Add("@date", SqlDbType.DateTime);
            cmd.Parameters["@date"].Value = date;
            cmd.CommandText = "SELECT r.Id, r.CompanyId, r.StartDate, r.EndDate, r.LocationId, l.Name as LName, r.CreateDate, r.UserId, c.Name as CName From Reservation r, Company c, Location l WHERE StartDate = @date AND r.CompanyId = c.Id AND R.LocationId = l.Id";
            cmd.Connection = con;

            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Reservation reservation = new Reservation();
                    reservation.Id = (int)reader["Id"];
                    reservation.StartDate = (DateTime)reader["StartDate"];
                    reservation.EndDate = (DateTime)reader["EndDate"];
                    reservation.CompanyId = (int)reader["CompanyId"];
                    reservation.LocationId = (int)reader["LocationId"];
                    reservation.CreateDate = (DateTime)reader["CreateDate"];
                    reservation.UserId = reader["UserId"].ToString();
                    reservation.CompanyName = reader["CName"].ToString();
                    reservation.LocationName = reader["LName"].ToString();
                    myReservations.Add(reservation);
                }
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return myReservations;
        }

        public static bool LocationAvailable(int locationId, DateTime startDate, DateTime endDate)
        {
            SqlConnection con = EF4_SQLserverDB.GetConnection();
            SqlCommand cmd = new SqlCommand();
            cmd.Parameters.Add("@startDate", SqlDbType.DateTime);
            cmd.Parameters["@startDate"].Value = startDate;
            cmd.Parameters.Add("@endDate", SqlDbType.DateTime);
            cmd.Parameters["@endDate"].Value = endDate;
            cmd.Parameters.Add("@locationId", SqlDbType.Int);
            cmd.Parameters["@locationId"].Value = locationId;
            cmd.CommandText = "SELECT count(r.Id) From Reservation r, Location l WHERE r.LocationId = l.Id AND @locationId = l.Id AND @startDate >= StartDate AND @endDate <= EndDate ";
            cmd.Connection = con;

            try
            {
                con.Open();
                int rows = (int)cmd.ExecuteScalar();
                if (rows >= 1)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }


        public static bool ContractLimit(int locationId, DateTime startDate, DateTime endDate)
        {
            SqlConnection con = EF4_SQLserverDB.GetConnection();
            SqlCommand cmd = new SqlCommand();
            cmd.Parameters.Add("@startDate", SqlDbType.DateTime);
            cmd.Parameters["@startDate"].Value = startDate;
            cmd.Parameters.Add("@endDate", SqlDbType.DateTime);
            cmd.Parameters["@endDate"].Value = endDate;
            cmd.Parameters.Add("@locationId", SqlDbType.Int);
            cmd.Parameters["@locationId"].Value = locationId;
            cmd.CommandText = "SELECT count(r.Id) From Reservation r, Location l WHERE r.LocationId = l.Id AND @locationId = l.Id AND @startDate >= StartDate AND @endDate <= EndDate ";
            cmd.Connection = con;

            try
            {
                con.Open();
                int rows = (int)cmd.ExecuteScalar();
                if (rows >= 1)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

    }
}
